import pymysql

# 获取连接对象conn，建立数据库的连接
def get_conn():
    conn = pymysql.connect(host='localhost',port=3306,user='root',passwd='password',db='test02')    # db:表示数据库名称
    return conn
def insert(params):
    conn = get_conn()
    cur = conn.cursor()
    sql2 = "insert into test02_first_tb(id,name) VALUES(%s,%s)"
    result = cur.execute(sql2,params)
    print(result)
    conn.commit()
    cur.close()
    conn.close()
def search(sql):
    conn = get_conn()
    cur = conn.cursor()
    reCount = cur.execute(sql)
    print(reCount)
    data = cur.fetchall()
    print(data)

    cur.close()
    conn.close()

if __name__ == '__main__':
    # li = [(1,'qiu'),(2,'da'),(3,'fei')]
    # insert(li)
    # search_sql = "select * from test02_first_tb"
    # search(search_sql)
    conn = get_conn()
    cur = conn.cursor()
    cur.execute("show databases;")
    #cur.execute("use database_name;")
    # cur.execute("select * from test02_first_tb")
    # # 使用 fetchone() 方法获取单条数据;使用 fetchall() 方法获取所有数据
    # data = cur.fetchall()
    # for item in data:
    #     print(item)
    # 插入数据
    sql2 = "insert into test(id,Name) VALUES(%s,%s)"  # sql语句，%s是占位符（%s是唯一的，不论什么数据类型都使用%s）用来防止sql注入
    params = ('qiu', 'dafei')  # 参数
    #reCount = cur.execute(sql2, params)
    # 批量插入
    # li = [('a1', 'b1'), ('a2', 'b2')]
    # sql3 = 'insert into test(id,Name) VALUES (%s,%s)'
    # reCount = cur.executemany(sql3, li)
    #conn.commit()  # 提交，执行多条命令只需要commit一次就行了
    #new_id = cur.lastrowid  # 获取自增id，提交完之后才能取到值
    #print(new_id)
    sql = "DELETE FROM test WHERE id = 'qiu'"
    conn.commit()
    print(cur.rowcount, " 条记录删除")
    cur.execute(sql)
    cur.close()
    conn.close()